/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.webproject.jsp.board;

import com.webproject.jsp.board.model.Board;
import com.webproject.jsp.common.CommonException;
import com.webproject.jsp.common.CommonUtil;
import com.webproject.jsp.common.JDriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author jeong
 */
public class BoardManager {
    
    public static BoardManager instance = new BoardManager();
    
    public static BoardManager getInstance() {
        return instance;
    }
    
    public BoardManager() {    
    }
    /**
     * 
     * @param board
     * @throws CommonException
     * @throws Exception 
     */
    public void insertBoard(Board board) throws CommonException, Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = JDriverManager.getConnection();
            pstmt = conn.prepareStatement(
                    "insert into BOARD01(title, content, passwrd, reg_user_id, reg_date)"
                    + " values(?, ?, ?, ?, now()) ");
            
            pstmt.setString(1, board.getTitle());
            pstmt.setString(2, board.getContent());
            pstmt.setString(3, board.getPassword());
            pstmt.setString(4, board.getRegUserId());
            
            pstmt.executeUpdate();
            
        } catch (SQLException se) {
            
            throw new CommonException("insertBoard",se);
            
        } finally {
            
            if(pstmt != null) try { pstmt.close(); } catch(SQLException se) {}
            if(conn != null) try { conn.close(); } catch(SQLException se) {}
            
        }
        
    }
    
    /**
     * 
     * @param no
     * @return
     * @throws CommonException
     * @throws Exception 
     */
    public Board getBoardSelect(int no) throws CommonException, Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            
            conn = JDriverManager.getConnection();
            
            pstmt = conn.prepareStatement(
                    "SELECT num, title, content, passwrd, reg_user_id, reg_date"
                  + " FROM BOARD01 WHERE num = ?");
            pstmt.setInt(1, no);
            rs = pstmt.executeQuery();
            
            if(rs.next()) {
                Board board = new Board();
                board.setNo(rs.getInt("num"));
                board.setTitle(rs.getString("title"));
                board.setContent(rs.getString("content"));
                board.setPassword(rs.getString("passwrd"));
                board.setRegUserId(rs.getString("reg_user_id"));
                board.setRegDate(rs.getString("reg_date"));
                
                return board;                
            } else {
                
                return null;
            }
            
        } catch(SQLException se) {
            
            throw new CommonException("getBoardSelect", se);
            
        } finally {
            if(rs != null) try { rs.close(); } catch(SQLException ex) {}            
            if(pstmt != null) try { pstmt.close(); } catch(SQLException se) {}
            if(conn != null) try { conn.close(); } catch(SQLException se) {}            
        }
    }    
    
    /**
     * 
     * @return
     * @throws CommonException
     * @throws Exception 
     */
    public List getBoardList() throws CommonException, Exception {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            
            conn = JDriverManager.getConnection();
            
            String sql = "SELECT NUM, TITLE, CONTENT, PASSWRD, REG_USER_ID, REG_DATE"
                       + " FROM BOARD01";
            
            stmt = conn.createStatement();
            rs   = stmt.executeQuery(sql);

            if(rs.next()) {
            
            List list = new ArrayList();
            
                do {

                    Board board = new Board();
                    board.setNo(rs.getInt("NUM"));
                    board.setTitle(CommonUtil.strChkNull(rs.getString("TITLE"),""));
                    board.setContent(CommonUtil.strChkNull(rs.getString("CONTENT"),""));
                    board.setPassword(CommonUtil.strChkNull(rs.getString("PASSWRD"),""));
                    board.setRegUserId(CommonUtil.strChkNull(rs.getString("REG_USER_ID"),""));
                    board.setRegDate(CommonUtil.strChkNull(rs.getString("REG_DATE"),""));

                    list.add(board);
                    
                } while(rs.next());
            
                return list;
                        
            } else {    
                return null;
            }
        } catch(SQLException se) {
            
            throw new CommonException("getBoardList",se);
            
        } finally {
            if(rs != null) try { rs.close(); } catch(SQLException ex) {}            
            if(stmt != null) try { stmt.close(); } catch(SQLException se) {}
            if(conn != null) try { conn.close(); } catch(SQLException se) {}            
        }
    }
    
    /**
     * 
     * @param board
     * @throws CommonException
     * @throws Exception 
     */
    public void updateBoard(Board board) throws CommonException, Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = JDriverManager.getConnection();
            pstmt = conn.prepareStatement(
                    "update BOARD01 set title = ?, content = ?, passwrd = ?"
                    + " where num = ?");
            
            pstmt.setString(1, board.getTitle());
            pstmt.setString(2, board.getContent());
            pstmt.setString(3, board.getPassword());
            pstmt.setInt(4, board.getNo());
            
            pstmt.executeUpdate();
            
        } catch (SQLException se) {
            
            throw new CommonException("insertBoard",se);
            
        } finally {
            
            if(pstmt != null) try { pstmt.close(); } catch(SQLException se) {}
            if(conn != null) try { conn.close(); } catch(SQLException se) {}
            
        }
        
    }
    
    /**
     * 
     * @param no
     * @throws CommonException
     * @throws Exception 
     */
    public void deleteBoard(String no) throws CommonException, Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = JDriverManager.getConnection();
            pstmt = conn.prepareStatement(
                    "DELETE FROM BOARD01 WHERE num = ?");
            
            pstmt.setInt(1, Integer.parseInt(no));
            
            pstmt.executeUpdate();
            
        } catch (SQLException se) {
            
            throw new CommonException("insertBoard",se);
            
        } finally {
            
            if(pstmt != null) try { pstmt.close(); } catch(SQLException se) {}
            if(conn != null) try { conn.close(); } catch(SQLException se) {}
            
        }
        
    }    
    
}
